﻿using System;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;


namespace SMSRouter
{
    public class DatabaseManager
    {
        //TimeControlServerDataSetTableAdapters.LogMessageAdapter logAdapter = new TimeControlServerDataSetTableAdapters.LogMessageAdapter();
        private string _connectionString = string.Empty;
        public string connectionString
        {
            get
            {
                lock (_connectionString)
                {
                    return _connectionString;
                }
            }
            set
            {
                lock (_connectionString)
                {
                    _connectionString = value;
                }
            }
        }
        public bool isConnected = false;
        
        public DatabaseManager()
        {
            //connectionString = getConnectionString();
        }


        public bool TestConnection()
        {
            bool canConnect = false;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    if (connectionString != string.Empty)
                    {
                        connection.Open();
                        canConnect = true;
                        connection.Close();
                    }
                }
                catch (SqlException) { }
            }
            return canConnect;
        }
        

        private string getConnectionString()
        {
            string _connectionString = "";

            foreach (ConnectionStringSettings connection in ConfigurationManager.ConnectionStrings)
            {
                if (connection.Name == "SMSRouter.Properties.Settings.DatabaseConnectionString")
                    _connectionString = connection.ConnectionString;
            }
            return _connectionString;
        }

        public void ProcessMessage(Message mes)
        {
            if (TestConnection())
            {
                using (SqlConnection sqlConnection1 = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = sqlConnection1;
                    /*cmd.CommandText = "insert into [Messages] (id, [from], [to], [text], ts, LogicalFolderId) " +
                                        "select @id, @from, @to, @text, @ts, id from dbo.LogicalFolders where Name = 'Outbox'";*/

                    cmd.CommandText = "merge [Messages] as target " +
                        "using (select @id as id, @from as [From], @to as [To], @text as [text], @ts as ts, lf.id as FolderId " +
                        "from dbo.LogicalFolders lf where lf.Name = @folderName) as source " +
                        "on target.Id = source.Id " +
                        "when not matched then " +
                        "insert (Id, [from],	[to],	[text],	TS,	LogicalFolderId) " +
                        "values(source.id, source.[from], source.[to], source.[text], source.TS, source.FolderId) " +
                        "when matched then " +
                        "update set LogicalFolderId = source.FolderId;";
                    cmd.Parameters.AddWithValue("@id", mes.id);
                    cmd.Parameters.AddWithValue("@from", mes.From);
                    cmd.Parameters.AddWithValue("@to", mes.To);
                    cmd.Parameters.AddWithValue("@text", mes.text);
                    cmd.Parameters.AddWithValue("@ts", mes.timeStamp);
                    cmd.Parameters.AddWithValue("@folderName", mes.folder);
                    sqlConnection1.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void UpdateMessage(Message mes)
        {
            if (TestConnection())
            {
                using (SqlConnection sqlConnection1 = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = sqlConnection1;
                    cmd.CommandText = "update m set LogicalFolderId = lf.id from [Messages] m cross join dbo.LogicalFolders lf where lf.Name = 'Send' and m.id = @id";
                    cmd.Parameters.AddWithValue("@id", mes.id);
                    sqlConnection1.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public List<Message> GetMessages()
        {
            List<Message> resultList = new List<Message>();
            DataTable dt = new DataTable();

            if (TestConnection())
            {
                SqlConnection sqlConnection1 = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand();
                //SqlDataReader reader;
                cmd.CommandText = "select m.id,[from], [to],[text],ts, lf.Name as FolderName from [Messages] m " +
                                "inner join LogicalFolders lf " +
                                "on m.LogicalFolderId = lf.Id " +
                "order by lf.Name, ts desc";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;
                sqlConnection1.Open();
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                }
                DataTableReader datatableReader = dt.CreateDataReader();
                while (datatableReader.Read())
                {
                    //string folder = datatableReader["FolderName"].ToString();
                    Message mes = new Message();
                    mes.id = new Guid(datatableReader["id"].ToString());
                    mes.From = datatableReader["from"].ToString();
                    mes.To = datatableReader["to"].ToString();
                    mes.text = datatableReader["text"].ToString();
                    mes.timeStamp = Convert.ToDateTime(datatableReader["ts"].ToString());
                    mes.folder = datatableReader["FolderName"].ToString();
                    resultList.Add(mes);
                }
            }
            return resultList;
        }

    }
}
